package com.fly.estore.dao.Impl;

import com.fly.estore.dao.BaseDAO;
import com.fly.estore.dao.MemberDAO;
import com.fly.estore.domain.MemberInfo;
import com.fly.estore.domain.MemberLevel;
import org.apache.commons.io.output.StringBuilderWriter;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Fly on 2016/4/8.
 */
@Repository("MemberDAO")
public class MemberDAOImpl extends BaseDAO<MemberInfo> implements MemberDAO {

    public int addMember(final MemberInfo memberInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into member_info(member_id, member_no, member_name, gender, birthday, mobile, email, qq, address, level, remark, create_id, create_time)");
        sql.append("values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        int update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, memberInfo.getMemberId());
                ps.setString(i++, memberInfo.getMemberNo());
                ps.setString(i++, memberInfo.getMemberName());
                ps.setString(i++, memberInfo.getGender());
                ps.setString(i++, memberInfo.getBirthday());
                ps.setString(i++, memberInfo.getMobile());
                ps.setString(i++, memberInfo.getEmail());
                ps.setString(i++, memberInfo.getQq());
                ps.setString(i++, memberInfo.getAddress());
                ps.setString(i++, memberInfo.getLevel());
                ps.setString(i++, memberInfo.getRemark());
                ps.setString(i++, memberInfo.getCreateId());
                ps.setString(i++, memberInfo.getCreateTime());
            }
        });
        return update;
    }

    public int updateMember(final MemberInfo memberInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("update member_info set")
                .append(" member_no = ?,")
                .append(" member_name = ?,")
                .append(" gender = ?,")
                .append(" birthday = ?,")
                .append(" mobile = ?,")
                .append(" email = ?,")
                .append(" qq = ?,")
                .append(" address = ?,")
                .append(" level = ?,")
                .append(" remark = ?,")
                .append(" last_update_id = ?,")
                .append(" last_update_time = ?")
                .append(" where member_id = ?");
        int update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, memberInfo.getMemberNo());
                ps.setString(i++, memberInfo.getMemberName());
                ps.setString(i++, memberInfo.getGender());
                ps.setString(i++, memberInfo.getBirthday());
                ps.setString(i++, memberInfo.getMobile());
                ps.setString(i++, memberInfo.getEmail());
                ps.setString(i++, memberInfo.getQq());
                ps.setString(i++, memberInfo.getAddress());
                ps.setString(i++, memberInfo.getLevel());
                ps.setString(i++, memberInfo.getRemark());
                ps.setString(i++, memberInfo.getLastUpdateId());
                ps.setString(i++, memberInfo.getLastUpdateTime());
                ps.setString(i++, memberInfo.getMemberId());
            }
        });
        return update;
    }

    public int deleteById(final String memberId) {
        String sql = "delete from member_info where member_id = ?";
        int update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, memberId);
            }
        });
        return update;
    }

    public MemberInfo getById(String memberId) {
        MemberInfo memberInfo = null;
        String sql = "select m.*, ml.member_level_id, ml.level_name, ml.discount from member_info m left join member_level ml on m.level = ml.member_level_id where member_id = ? order by create_time desc";
        List<MemberInfo> resultList = getJdbcTemplate().query(sql, new MemberRowMapper(), new Object[]{memberId});
        if (!CollectionUtils.isEmpty(resultList)){
            memberInfo = resultList.get(0);
        }
        return memberInfo;
    }

    public List<MemberInfo> queryMemberList(String memberNo, String memberName, String mobile) {
        StringBuilder sql = new StringBuilder();
        sql.append("select m.*, ml.member_level_id, ml.level_name, ml.discount from member_info m")
            .append(" left join member_level ml on m.level = ml.member_level_id")
            .append(" where 1 = 1");
        List params = new ArrayList();
        if (StringUtils.isNotBlank(memberNo)){
            sql.append(" and member_no like ?");
            params.add("'%" + memberName + "%'");
        }
        if (StringUtils.isNotBlank(memberName)){
            sql.append(" and member_name like ?");
            params.add("'%" + memberName + "%'");
        }
        if (StringUtils.isNotBlank(mobile)){
            sql.append(" and mobile like ?");
            params.add("'%" + mobile + "%'");
        }
        sql.append(" order by create_time desc");
        List<MemberInfo> memberList = getJdbcTemplate().query(sql.toString(), new MemberRowMapper(), params.toArray());
        return memberList;
    }

    public List<MemberInfo> queryMemberList(String query) {
        StringBuilder sql = new StringBuilder();
        sql.append("select m.*, ml.member_level_id, ml.level_name, ml.discount from member_info m")
            .append(" left join member_level ml on m.level = ml.member_level_id")
            .append(" where 1 = 1");
        List params = new ArrayList();
        if (StringUtils.isNotBlank(query)){
            sql.append(" and (member_no like ? or member_name like ? or mobile like ?)");
            params.add("'%" + query + "%'");
            params.add("'%" + query + "%'");
            params.add("'%" + query + "%'");
        }
        sql.append(" order by create_time desc");
        List<MemberInfo> memberList = getJdbcTemplate().query(sql.toString(), new MemberRowMapper(), params.toArray());
        return memberList;
    }

    public int getMemberCount(String date) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(member_id) from member_info where 1 = 1");
        List params = new ArrayList();
        if (StringUtils.isNotBlank(date)){
            sql.append(" and create_time > ?");
            params.add(date);
        }
        int totalCount = getJdbcTemplate().queryForObject(sql.toString(), params.toArray(), Integer.class);
        return totalCount;
    }

    public class MemberRowMapper implements RowMapper<MemberInfo> {
        public MemberInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            MemberInfo memberInfo = new MemberInfo();
            memberInfo.setMemberId(rs.getString("member_id"));
            memberInfo.setMemberName(rs.getString("member_name"));
            memberInfo.setMemberNo(rs.getString("member_no"));
            memberInfo.setGender(rs.getString("gender"));
            memberInfo.setBirthday(rs.getString("birthday"));
            memberInfo.setMobile(rs.getString("mobile"));
            memberInfo.setEmail(rs.getString("email"));
            memberInfo.setQq(rs.getString("qq"));
            memberInfo.setAddress(rs.getString("address"));
            memberInfo.setLevel(rs.getString("level"));
            MemberLevel memberLevel = new MemberLevel();
            memberLevel.setMemberLevelId(rs.getString("member_level_id"));
            memberLevel.setLevelName(rs.getString("level_name"));
            memberLevel.setDiscount(rs.getFloat("discount"));
            memberInfo.setMemberLevel(memberLevel);
            memberInfo.setCreateTime(rs.getString("create_time"));
            memberInfo.setCreateId(rs.getString("create_id"));
            memberInfo.setLastUpdateTime(rs.getString("last_update_time"));
            memberInfo.setLastUpdateId(rs.getString("last_update_id"));
            return memberInfo;
        }
    }
}
